﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SlamDunk.CMS.Common;

namespace SlamDunk.CMS.DAL
{
    /// <summary>
    /// Article数据访问层类（手写部分类）
    /// </summary>
    public partial class ArticleDal : IDAL.IArticleDal
    {
        #region 01.根据栏目Id获取文章列表
        /// <summary>
        /// 根据栏目Id获取文章列表
        /// </summary>
        /// <param name="channelId">栏目Id</param>
        /// <returns>文章列表</returns>
        public List<Model.Article> GetModelList(int channelId)
        {
            List<Model.Article> list = new List<Model.Article>();
            string sql = "SELECT * FROM Articles WHERE DelFlag = 0 AND ChannelId = @ChannelId";
            using (SqlDataReader reader = DbHelper.ExecuteReader(sql, new SqlParameter("@ChannelId", channelId)))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 02.根据文章Id与栏目Id查询文章的索引
        /// <summary>
        /// 根据文章Id与栏目Id查询文章的索引
        /// </summary>
        /// <param name="id">文章Id</param>
        /// <param name="channelId">栏目Id</param>
        /// <returns>文章索引</returns>
        public int GetRowIndex(int id, int channelId)
        {
            string sql = "SELECT RowIndex FROM (SELECT ROW_NUMBER() OVER (ORDER BY IsTop DESC, Click DESC, Sort DESC, AddTime DESC) AS RowIndex, * FROM dbo.Articles  WHERE ChannelId = @ChannelId) AS T WHERE T.Id = @Id";
            DataTable dateTable = DbHelper.GetDataTable(sql, new SqlParameter("@ChannelId", channelId), new SqlParameter("@Id", id));
            int rowIndex = Convert.ToInt32(dateTable.Rows[0][0]);
            return rowIndex;
        }
        #endregion

        #region 03.获取上一篇文章
        /// <summary>
        /// 获取上一篇文章
        /// </summary>
        /// <param name="id">文章Id</param>
        /// <param name="channelId">栏目Id</param>
        /// <returns>文章实体对象</returns>
        public Model.Article GetPrevModel(int id, int channelId)
        {
            //获取文章索引
            int rowIndex = GetRowIndex(id, channelId);
            string sql = "SELECT TOP 1 * FROM (SELECT ROW_NUMBER() OVER (ORDER BY IsTop DESC, Click DESC, Sort DESC, AddTime DESC) AS RowIndex, * FROM dbo.Articles  WHERE ChannelId = @ChannelId) AS T WHERE T.RowIndex < @RowIndex ORDER BY RowIndex DESC";
            using (SqlDataReader reader = DbHelper.ExecuteReader(sql, new SqlParameter("@ChannelId", channelId), new SqlParameter("@RowIndex", rowIndex)))
            {
                if (reader.Read())
                {
                    return ToModel(reader);
                }
                else
                {
                    return null;
                }
            }
        }
        #endregion

        #region 04.获取下一篇文章
        /// <summary>
        /// 获取下一篇文章
        /// </summary>
        /// <param name="id">文章Id</param>
        /// <param name="channelId">栏目Id</param>
        /// <returns>文章实体对象</returns>
        public Model.Article GetNextModel(int id, int channelId)
        {
            //获取文章索引
            int rowIndex = GetRowIndex(id, channelId);
            string sql = "SELECT TOP 1 * FROM (SELECT ROW_NUMBER() OVER (ORDER BY IsTop DESC, Click DESC, Sort DESC, AddTime DESC) AS RowIndex, * FROM dbo.Articles  WHERE ChannelId = @ChannelId) AS T WHERE T.RowIndex > @RowIndex ORDER BY RowIndex ASC";
            using (SqlDataReader reader = DbHelper.ExecuteReader(sql, new SqlParameter("@ChannelId", channelId), new SqlParameter("@RowIndex", rowIndex)))
            {
                if (reader.Read())
                {
                    return ToModel(reader);
                }
                else
                {
                    return null;
                }
            }
        }
        #endregion

        #region 04.根据栏目Id、开始时间、结束时间与标题关键字查询文章记录条数
        /// <summary>
        /// 根据栏目Id、开始时间、结束时间与标题关键字查询文章记录条数
        /// </summary>
        /// <param name="channelId">栏目Id</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <param name="title">标题关键字</param>
        /// <returns>符合条件的文章记录条数</returns>
        public int GetCount(int channelId, string startTime, string endTime, string title)
        {
            string sql = "SELECT COUNT(*) FROM dbo.Articles WHERE ChannelId = @ChannelId";

            #region 非空校验
            if (!startTime.IsNullOrEmpty())
            {
                sql = string.Format("{0} AND AddTime >= '{1}'", sql, startTime.FilterSql());
            }
            if (!endTime.IsNullOrEmpty())
            {
                sql = string.Format("{0} AND AddTime <= '{1}'", sql, endTime.FilterSql());
            }
            if (!title.IsNullOrEmpty())
            {
                sql = string.Format("{0} AND Title LIKE '%{1}%'", sql, title.FilterSql());
            }
            #endregion

            return (int)DbHelper.ExecuteScalar(sql, new SqlParameter("@ChannelId", channelId));
        }
        #endregion

        #region 05.根据栏目Id、开始时间、结束时间与标题关键字分页查询文章列表
        /// <summary>
        /// 根据栏目Id、开始时间、结束时间与标题关键字查询文章列表
        /// </summary>
        /// <param name="start">起始行</param>
        /// <param name="end">结束行</param>
        /// <param name="channelId">栏目Id</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <param name="title">标题关键字</param>
        /// <returns>文章列表</returns>
        public List<Model.Article> GetModelList(int start, int end, int channelId, string startTime, string endTime, string title)
        {
            List<Model.Article> list = new List<Model.Article>();
            string sql = "SELECT *, ROW_NUMBER() OVER(ORDER BY IsTop DESC, Click DESC, Sort DESC, AddTime DESC) AS RowIndex  FROM dbo.Articles WHERE ChannelId = @ChannelId";

            #region 非空校验
            if (!startTime.IsNullOrEmpty())
            {
                sql = string.Format("{0} AND AddTime >= '{1}'", sql, startTime.FilterSql());
            }
            if (!endTime.IsNullOrEmpty())
            {
                sql = string.Format("{0} AND AddTime <= '{1}'", sql, endTime.FilterSql());
            }
            if (!title.IsNullOrEmpty())
            {
                sql = string.Format("{0} AND Title LIKE '%{1}%'", sql, title.FilterSql());
            }
            #endregion

            //分页处理
            sql = string.Format("SELECT * FROM ({0}) AS T WHERE T.RowIndex >= @Start AND T.RowIndex <= @End", sql);

            SqlParameter[] args = new SqlParameter[] {
                new SqlParameter("@ChannelId", channelId),
                new SqlParameter("@Start", start),
                new SqlParameter("@End", end)
            };

            using (SqlDataReader reader = DbHelper.ExecuteReader(sql, args))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 06.根据栏目Id获取文章记录条数
        /// <summary>
        /// 根据栏目Id获取文章记录条数
        /// </summary>
        /// <param name="channelId">栏目Id</param>
        /// <returns>符合条件的文章记录条数</returns>
        public int GetCount(int channelId)
        {
            string sql = "SELECT COUNT(*) FROM dbo.Articles WHERE ChannelId = @ChannelId";
            return (int)DbHelper.ExecuteScalar(sql, new SqlParameter("@ChannelId", channelId));
        }
        #endregion

        #region 07.根据栏目Id分页查询文章列表
        /// <summary>
        /// 根据栏目Id分页查询文章列表
        /// </summary>
        /// <param name="start">起始行</param>
        /// <param name="end">终止行</param>
        /// <param name="channelId">栏目Id</param>
        /// <returns>文章列表</returns>
        public List<Model.Article> GetModelList(int start, int end, int channelId)
        {
            List<Model.Article> list = new List<Model.Article>();
            string sql = "SELECT *, ROW_NUMBER() OVER(ORDER BY IsTop DESC, Click DESC, Sort DESC, AddTime DESC) AS RowIndex  FROM dbo.Articles WHERE ChannelId = @ChannelId";
            //分页处理
            sql = string.Format("SELECT * FROM ({0}) AS T WHERE T.RowIndex >= @Start AND T.RowIndex <= @End", sql);

            SqlParameter[] args = new SqlParameter[] {
                new SqlParameter("@ChannelId", channelId),
                new SqlParameter("@Start", start),
                new SqlParameter("@End", end)
            };

            using (SqlDataReader reader = DbHelper.ExecuteReader(sql, args))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 08.根据栏目Id与数量查询文章列表
        /// <summary>
        /// 根据栏目Id与数量查询文章列表
        /// </summary>
        /// <param name="channelId">栏目Id</param>
        /// <param name="count">数量</param>
        /// <returns>文章列表</returns>
        public List<Model.Article> GetModelListByCount(int channelId, int count)
        {
            List<Model.Article> list = new List<Model.Article>();
            string sql = string.Format("SELECT TOP {0} * FROM dbo.Articles WHERE ChannelId = @ChannelId ORDER BY IsTop DESC, Click DESC, Sort DESC, AddTime DESC", count);
            using (SqlDataReader reader = DbHelper.ExecuteReader(sql, new SqlParameter("@ChannelId", channelId)))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion
    }
}
